#!/usr/bin/env python
#coding:utf-8
"""
  Author:   --<>
  Purpose: 
  Created: 05/12/18
"""
import sys
import xlrd

sys.path.append('.')
from mysqldb import Mysql


class toolbox:
    def __init__(self, dbconfig, xls_file):
        self.s_db = Mysql(dbconfig, _autoCount=1000)
        self.xls_file = xls_file
    
    def create_table_by_fields(self, table, fields):
        sc = ['create table if not exists `{}`('.format(table)]
        sc.append('`id` int(11) NOT NULL AUTO_INCREMENT,')
        for f in fields:
            fs = "`{}` varchar(200) NOT NULL DEFAULT '',".format(f.encode('utf8'))
            sc.append(fs)
        sc.append('PRIMARY KEY (`id`)')
        sc.append(') ENGINE=InnoDB DEFAULT CHARSET=utf8')
        return '\n'.join(sc)
        
    def run(self, table):
        wk = xlrd.open_workbook(self.xls_file)
        sheet = wk.sheet_by_index(0)
        nrows = sheet.nrows
        first_row = sheet.row_values(0)
        # first_row = ['' for i in range(3)]
        fields = []
        for f in first_row:
            if not f:
                f = 'unknown'
            if f in fields:
                for i in range(10):
                    if u'{}_{}'.format(f, i) not in fields:
                        f = u'{}_{}'.format(f, i)
                        break
            fields.append(f)
        fcount = len(fields)
        # table = 'svw_import_part_price'
        sc = self.create_table_by_fields(table, fields)
        self.s_db.execute(sc, _commit=True)
        for nr in range(1, nrows):
            row_values = sheet.row_values(nr)
            row = {}
            for i in range(fcount):
                val = str(row_values[i]) if isinstance(row_values[i], float) else row_values[i]
                if len(val) >= 200:
                    val = val[:190]
                row[fields[i]] = val
            sql = self.s_db.construct_insert_sql(table, row)
            self.s_db.execute(sql.encode('utf8'), _commit=False)
        self.s_db.commit()


if __name__ == '__main__':
    dbconfig = {
        'host': '192.168.10.166',
        'user': 'a',
        'passwd': '110',
        'db': 'honda',
	'port': 3307
    }
    local_db = {
    	'host': "127.0.0.1",
    	'user': 'root',
    	'passwd': '123456',
    	'db': 'my_data',
    	'port': 3306
    }

    xls_file = sys.argv[1]
    table = sys.argv[2]
    tb = toolbox(local_db, xls_file)
    tb.run(table)
    print 'DONE, GO HOME!!!'
